using System;
using System.Data;
using System.Data.SqlClient;
using PCSComMaterials.Inventory.DS;
using PCSComProduction.DCP.DS;
using PCSComProduction.WorkOrder.DS;
using PCSComUtils.Common;
using PCSComUtils.DataContext;
using System.Collections.Generic;
using System.Transactions;
using PCSComUtils.DataAccess;
using System.Linq;
using System.Text;
using PCSComUtils.PCSExc;

namespace PCSComProduction.WorkOrder.BO
{
    public class RequiredMaterialBO
    {
        /// <summary>
        /// Inserts the specified required master.
        /// </summary>
        /// <param name="requiredMaster">The required master.</param>
        /// <param name="details">The details.</param>
        /// <returns></returns>
        public int Insert(PRO_RequiredMaterialMaster requiredMaster, DataSet details)
        {
            const string methodName = "" + ".Insert()";
            try
            {
                using (var trans = new TransactionScope())
                {
                    using (var dcPCS = new PCSDataContext(Utils.Instance.ConnectionString))
                    {
                        foreach (DataRow dr in details.Tables[0].Rows)
                        {
                            var detail = new PRO_RequiredMaterialDetail
                            {
                                ProductID = Convert.ToInt32(dr[PRO_RequiredMaterialDetailTable.PRODUCTID_FLD]),
                                Qty = Convert.ToDecimal(dr[PRO_RequiredMaterialDetailTable.QUANTITY_FLD]),
                                WorkOrderDetailID = Convert.ToInt32(dr[PRO_RequiredMaterialDetailTable.WORKORDERDETAILID_FLD])
                            };
                            decimal requiredQty = 0;
                            if (dr[PRO_RequiredMaterialDetailTable.REQUIREDQUANTITY_FLD] != null && !string.IsNullOrEmpty(dr[PRO_RequiredMaterialDetailTable.REQUIREDQUANTITY_FLD].ToString()))
                            {
                                requiredQty = Convert.ToDecimal(dr[PRO_RequiredMaterialDetailTable.REQUIREDQUANTITY_FLD]);
                            }
                            detail.RequiredQty = requiredQty + detail.Qty;
                            if (dr[PRO_RequiredMaterialDetailTable.STOCKQUANTITY_FLD] != null && !string.IsNullOrEmpty(dr[PRO_RequiredMaterialDetailTable.STOCKQUANTITY_FLD].ToString()))
                            {
                                detail.StockQty = Convert.ToDecimal(dr[PRO_RequiredMaterialDetailTable.STOCKQUANTITY_FLD]);
                            }

                            // insert detail to master
                            requiredMaster.PRO_RequiredMaterialDetails.Add(detail);
                        }

                        // insert to database
                        dcPCS.PRO_RequiredMaterialMasters.InsertOnSubmit(requiredMaster);
                        dcPCS.SubmitChanges();
                        trans.Complete();
                        // return new id
                        return requiredMaster.RequiredMaterialMasterID;
                    }
                }
            }
            catch (SqlException ex)
            {
                if (ex.Errors.Count > 1)
                {
                    if (ex.Number == ErrorCode.SQLDUPLICATE_KEYCODE)
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
                    throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
                }
                throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
            }
        }

        /// <summary>
        ///     Update required material transaction
        /// </summary>
        /// <param name="inputMaster"></param>
        /// <param name="details"></param>
        public void Update(PRO_RequiredMaterialMaster inputMaster, DataSet details)
        {
            const string methodName = "" + ".Update()";
            try
            {
                using (var trans = new TransactionScope())
                {
                    using (var dcPCS = new PCSDataContext(Utils.Instance.ConnectionString))
                    {
                        #region update master information
                        
                        var requiredMaster = dcPCS.PRO_RequiredMaterialMasters.FirstOrDefault(r => r.RequiredMaterialMasterID == inputMaster.RequiredMaterialMasterID);
                        requiredMaster.IssuePurposeID = inputMaster.IssuePurposeID;
                        requiredMaster.LastUpdate = dcPCS.GetServerDate();
                        requiredMaster.ProductionLineID = inputMaster.ProductionLineID;
                        requiredMaster.SaleOrderDetailID = inputMaster.SaleOrderDetailID;
                        requiredMaster.TransDate = inputMaster.TransDate;
                        requiredMaster.TransNo = inputMaster.TransNo;
                        requiredMaster.UserApproval = inputMaster.UserApproval;
                        requiredMaster.Username = inputMaster.Username;

                        #endregion

                        foreach (DataRow dr in details.Tables[0].Rows)
                        {
                            if (dr.RowState == DataRowState.Deleted)
                            {
                                continue;
                            }

                            var detailId = Convert.ToInt32(dr[PRO_RequiredMaterialDetailTable.REQUIREDMATERIALDETAIID_FLD]);
                            var detail = dcPCS.PRO_RequiredMaterialDetails.FirstOrDefault(d => d.RequiredMaterialDetailID == detailId) ?? new PRO_RequiredMaterialDetail
                                                                                                                                              {
                                                                                                                                                  RequiredMaterialDetailID = 0
                                                                                                                                              };
                            detail.ProductID = Convert.ToInt32(dr[PRO_RequiredMaterialDetailTable.PRODUCTID_FLD]);
                            detail.Qty = Convert.ToDecimal(dr[PRO_RequiredMaterialDetailTable.QUANTITY_FLD]);
                            detail.WorkOrderDetailID = Convert.ToInt32(dr[PRO_RequiredMaterialDetailTable.WORKORDERDETAILID_FLD]);
                            
                            decimal requiredQty = 0;
                            if (dr[PRO_RequiredMaterialDetailTable.REQUIREDQUANTITY_FLD] != null && !string.IsNullOrEmpty(dr[PRO_RequiredMaterialDetailTable.REQUIREDQUANTITY_FLD].ToString()))
                            {
                                requiredQty = Convert.ToDecimal(dr[PRO_RequiredMaterialDetailTable.REQUIREDQUANTITY_FLD]);
                            }
                            // edit detail
                            if (detail.RequiredMaterialDetailID > 0)
                            {
                                detail.RequiredQty = detail.Qty;
                            }
                            else
                            {
                                detail.RequiredQty = requiredQty + detail.Qty;
                            }
                            if (dr[PRO_RequiredMaterialDetailTable.STOCKQUANTITY_FLD] != null && !string.IsNullOrEmpty(dr[PRO_RequiredMaterialDetailTable.STOCKQUANTITY_FLD].ToString()))
                            {
                                detail.StockQty = Convert.ToDecimal(dr[PRO_RequiredMaterialDetailTable.STOCKQUANTITY_FLD]);
                            }

                            // new detail
                            if (detail.RequiredMaterialDetailID == 0)
                            {
                                // insert detail to master
                                inputMaster.PRO_RequiredMaterialDetails.Add(detail);
                            }
                        }

                        // submit changes to database
                        dcPCS.SubmitChanges();
                        trans.Complete();
                    }
                }
            }
            catch (SqlException ex)
            {
                if (ex.Errors.Count > 1)
                {
                    if (ex.Number == ErrorCode.SQLDUPLICATE_KEYCODE)
                        throw new PCSDBException(ErrorCode.DUPLICATE_KEY, methodName, ex);
                    throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
                }
                throw new PCSDBException(ErrorCode.ERROR_DB, methodName, ex);
            }
        }
        
        public bool DeleteRequireMaterial(int iRequireMaterialID)
        {
            bool ret = true;
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    try
                    {
                        List<PRO_RequiredMaterialDetail> listDetail = db.PRO_RequiredMaterialDetails.Where(e => e.RequiredMaterialMasterID == iRequireMaterialID).ToList<PRO_RequiredMaterialDetail>();
                        StringBuilder strWorkOrderDetailID = new StringBuilder();
                        StringBuilder strProductID = new StringBuilder();
                        foreach (PRO_RequiredMaterialDetail objDetail in listDetail)
                        {
                            if (strWorkOrderDetailID.Length > 0) strWorkOrderDetailID.Append(",");
                            strWorkOrderDetailID.Append(objDetail.WorkOrderDetailID.ToString());
                            if (strProductID.Length > 0) strProductID.Append(",");
                            strProductID.Append(objDetail.ProductID.ToString());
                        }
                        #region Delete PRO_RequiredMaterial
                        db.PRO_RequiredMaterialDetails.DeleteAllOnSubmit(listDetail);
                        var obj = db.PRO_RequiredMaterialMasters.SingleOrDefault<PRO_RequiredMaterialMaster>(e => e.RequiredMaterialMasterID == iRequireMaterialID);
                        int iSaleOrderDetail = obj.SaleOrderDetailID;
                        db.PRO_RequiredMaterialMasters.DeleteOnSubmit(obj);
                        db.SubmitChanges();
                        #endregion

                        #region update PRO_RequiredMaterialDetail                    
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("UPDATE PRO_RequiredMaterialDetail");
                        strSql.Append("  SET RequiredQty =");
                        strSql.Append("  (SELECT SUM(Qty) FROM PRO_RequiredMaterialDetail D JOIN PRO_RequiredMaterialMaster M");
                        strSql.Append("  ON D.RequiredMaterialMasterID = M.RequiredMaterialMasterID");
                        strSql.Append("  WHERE D.ProductID = PRO_RequiredMaterialDetail.ProductID");
                        strSql.Append("  AND M.SaleOrderDetailID = {0}");
                        strSql.Append("  AND D.WorkOrderDetailID = PRO_RequiredMaterialDetail.WorkOrderDetailID");
                        strSql.Append("  AND IssuePurposeID = " + ((int)PurposeEnum.Plan).ToString() + ")");
                        strSql.Append("  WHERE RequiredMaterialMasterID IN (SELECT RequiredMaterialMasterID FROM PRO_RequiredMaterialMaster");
                        strSql.Append("  WHERE SaleOrderDetailID = " + iSaleOrderDetail.ToString());
                        strSql.Append("  AND ProductID IN (" + strProductID + ")");
                        strSql.Append("  AND WorkOrderDetailID IN (" + strWorkOrderDetailID + ")");
                        strSql.Append("  AND IssuePurposeID = " + ((int)PurposeEnum.Plan).ToString() + ")");
                        db.ExecuteCommand(strSql.ToString(), iSaleOrderDetail);
                        #endregion
                        db.SubmitChanges();
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                        ret = false;
                        Transaction.Current.Rollback(ex);
                    }
                    finally
                    {
                        trans.Complete();
                    }
                    return ret;
                }
            }
        }
        

        public DataSet GetDetailByMaster(int requiredMaterialMasterId)
        {
            return new PRO_RequiredMaterialDetailDS().GetDetailByMaster(requiredMaterialMasterId);
        }

        public bool CheckRequiredDetail(int pintRequireMaterialID)
        {
            IV_MiscellaneousIssueMasterDS dsIssueMaterialDetail = new IV_MiscellaneousIssueMasterDS();
            return dsIssueMaterialDetail.IsReceived(pintRequireMaterialID);
        }

        //[AutoComplete(true)]
        public string GetProductionLine(int pintProductionLineID)
        {
            PRO_ProductionLineDS dsPL = new PRO_ProductionLineDS();
            PRO_ProductionLineVO voPL = new PRO_ProductionLineVO();
            voPL = (PRO_ProductionLineVO)dsPL.GetObjectVO(pintProductionLineID);
            return voPL.Code;
        }

        //[AutoComplete(true)]
        public string GetPurposeCode(int pintPurposeID)
        {
            PRO_IssuePurposeDS dsIssuePurpose = new PRO_IssuePurposeDS();
            PRO_IssuePurposeVO voIssuePurpose = new PRO_IssuePurposeVO();
            voIssuePurpose = (PRO_IssuePurposeVO)dsIssuePurpose.GetObjectVO(pintPurposeID);
            return voIssuePurpose.Description;
        }

        //[AutoComplete(true)]
        public DataRow GetProductInfo(int pintSaleOrderDetailID)
        {
            DataRow drow = new PRO_RequiredMaterialMasterDS().GetProductInfo(pintSaleOrderDetailID);
            return drow;
        }

        //[AutoComplete(true)]
        public int UpdateAndReturnID(PRO_RequiredMaterialMasterVO masterVO, DataSet dataSet)
        {
            // Update master again
            PRO_RequiredMaterialMasterDS dsMaster = new PRO_RequiredMaterialMasterDS();
            dsMaster.Update(masterVO);
            // Update Detail
            PRO_RequiredMaterialDetailDS dsDetail = new PRO_RequiredMaterialDetailDS();
            dsDetail.UpdateDataSet(dataSet);
            return masterVO.RequiredMaterialMasterID;
        }
        //[AutoComplete(true)]
        public bool IsReceived(int iRequiredMasterID)
        {
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    var list = (from obj in db.PRO_RequiredMaterialDetails
                                where (obj.ActualReceived == null || obj.ActualReceived == 0)
                                && obj.RequiredMaterialMasterID == iRequiredMasterID
                                select obj.ActualReceived).Count();
                    trans.Complete();
                    if (list > 0)
                        return false;
                    else
                        return true;
                }
            }
        }
        //[AutoComplete(true)]
        public DataTable GetSlipData(int pintMasterID)
        {
            PRO_RequiredMaterialDetailDS dsRequire = new PRO_RequiredMaterialDetailDS();
            return dsRequire.GetSlipData(pintMasterID);
        }

        public bool ConfirmProduction(string pstrUserApproval, int iRequiredMasterID)
        {
            bool ret = true;
            using (var trans = new TransactionScope())
            {
                using (var db = new PCSDataContext(Utils.Instance.ConnectionString))
                {
                    try
                    {
                        var obj = db.PRO_RequiredMaterialMasters.SingleOrDefault(e => e.RequiredMaterialMasterID == iRequiredMasterID);
                        obj.UserApproval = pstrUserApproval;
                        db.SubmitChanges();
                    }
                    catch (Exception ex) { throw ex; ret = false; }
                    finally { trans.Complete(); }
                    return ret;
                }
            }
        }
        //[AutoComplete(true)]
        public DataTable GetRequiredBySale(int pintSaleOrderDetailID)
        {
            PRO_RequiredMaterialDetailDS dsDetail = new PRO_RequiredMaterialDetailDS();
            return dsDetail.GetRequiredBySale(pintSaleOrderDetailID);
        }
    }
}
